#!/usr/bin/python2.7.5 
# -*- coding: utf-8 -*-
# 
# FileName : into_excel.py
# Author   : Fish
# DateTime : 2018/11/1 19:08

from openpyxl import Workbook
import datetime
import MySQLdb
from DBUtils.PooledDB import PooledDB
import ConfigParser
from xlrd import open_workbook
from xlutils.copy import copy
import xlwt
import time
import schedule
import logging
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.audio import MIMEAudio
from email.mime.application import MIMEApplication
import mimetypes
import os

conf_file_name = "Conf/config.ini"
out_file_path = "templates/excel_temp/"
execel_temp_name = out_file_path + "temp.xlsx"
#mail_host = "smtp.gmail.com"
#mail_port = 25
#mail_host = "smtp.qq.com"
#mail_port = 465
#from_add = "3525733479@qq.com"
#_pwd = "senzzxrszizucjcd"
#to_add = ['9651910@qq.com', '3525733479@qq.com', 'coming009@gmail.com','David10495@hotmail.com']

mail_host = "smtp.exmail.qq.com"
mail_port = 465
from_add = "robot@homeequityplatform.ca"
_pwd = "Huangmin1234"
to_add = ['info@homeequityplatform.ca', 'robot@homeequityplatform.ca']

logging.basicConfig(level=logging.INFO,format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %Y %H:%M:%S')

def get_config(filename):
    config = {}
    cf = ConfigParser.ConfigParser()
    cf.read(filename)
    config['host'] = cf.get("db","db_host")
    config['port'] = int(cf.get("db","db_port"))
    config['user'] = cf.get("db","db_user")
    config['passwd'] = cf.get("db","db_password")
    config['db'] = cf.get("db","db_name")
    
    return config

class DbManager(object):

    def __init__(self):
        db_config = get_config(conf_file_name)
        #self._pool = PooledDB(MySQLdb, mincached=0, maxcached=10, maxshared=10, maxusage=10000, **connKwargs) 
        self.__pool = PooledDB(creator=MySQLdb, host=db_config['host'], \
                    port=db_config['port'], \
                    user=db_config['user'], \
                    passwd=db_config['passwd'], \
                    db=db_config['db'], \
                    mincached=0, maxcached=10, maxshared=10, maxusage=10000)

    def getConn(self):
        return self.__pool.connection()

_dbManager = DbManager()

def getConn():
    return _dbManager.getConn()

def executeAndGetId(sql, param=None):
    """excute sql and return autoincreaed id"""
    conn=getConn()
    cursor=conn.cursor()
    if param == None:
        cursor.execute(sql)
    else:
        cursor.execute(sql, param)
    id = cursor.lastrowid
    cursor.close()
    conn.close()
    return id

def execute(sql, param=None):
    """execute sql"""
    conn = getConn()
    cursor = conn.cursor()

    if param == None:
        rowcount = cursor.execute(sql)
    else:
        rowcount = cursor.execute(sql, param)
    cursor.close()
    conn.close()

    return rowcount

def queryOne(sql,param=None):
    """ fetch one record """
    conn = getConn()
    cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    if param == None:
        rowcount = cursor.execute(sql)
    else:
        rowcount = cursor.execute(sql, param)
    
    if rowcount > 0:
        res_dict = cursor.fetchone()
    else:
        res_dict = None
    
    cursor.close()
    conn.close()

    return res_dict

def queryAll(sql, param=None):
    """ fetch records """
    conn = getConn()
    #cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    cursor = conn.cursor()

    if param == None:
        rowcount = cursor.execute(sql)
    else:
        rowcount = cursor.execute(sql, param)
    
    if rowcount > 0:
        res = cursor.fetchall()
    else:
        res = None
    
    cursor.close()
    conn.close()

    return rowcount,res

def queryAllwithDict(sql, param=None):
    """ fetch records """
    conn = getConn()
    cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    #cursor = conn.cursor()

    if param == None:
        rowcount = cursor.execute(sql)
    else:
        rowcount = cursor.execute(sql, param)
    
    if rowcount > 0:
        res = cursor.fetchall()
    else:
        res = None
    
    cursor.close()
    conn.close()

    return rowcount,res

def write_into_excel():
    today_format = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    tb = open_workbook(execel_temp_name)
    nb = copy(tb)
    #get the new sheet
    ns = nb.get_sheet(0)
    """
    add definition of font, date, datetime
    add by fishjhuang on 1/12/2018
    """
    #define font
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.height = 280
    #define normal style
    normal_style = xlwt.XFStyle()
    normal_style.font = font
    #define date style
    date_style = xlwt.XFStyle()
    date_style.num_format_str = 'yyyy/mm/dd'
    date_style.font = font
    #define datetime style
    datetime_style = xlwt.XFStyle()
    datetime_style.num_format_str = 'yyyy/mm/dd hh:mm:ss'
    datetime_style.font = font
    #get the info from db
    count,res = queryAll('select * from customers where email_sent_indicator = "0"')
    print count, res
    if count > 0:
        for i in range(count):
            res_tuple = res[i]
            print res_tuple
            row = i + 1
            print row
            j = 0
            for value in res_tuple:
                print row,j,value
                #ns.write(row,j,value)
                if j in (3, 25):
                    ns.write(row,j,value,date_style)
                elif j == 33:
                    ns.write(row,j,value,datetime_style)
                else:
                    ns.write(row,j,value,normal_style)
                j+=1
    file_name = time.strftime('%Y-%m-%d',time.localtime(time.time()))+".xls"
    nb_file_name = out_file_path + time.strftime('%Y-%m-%d',time.localtime(time.time()))+".xls"
    logging.info(nb_file_name)
    logging.info(file_name)
    #print nb_file_name
    nb.save(nb_file_name)
    logging.info(time.strftime('%Y.%m.%d %H:%M:%S',time.localtime(time.time())) + " excel create successfully.")
    #print time.strftime('%Y.%m.%d %H:%M:%S',time.localtime(time.time())) + " excel create successfully."
    return nb_file_name, file_name, count

def send_email(file_path, file_name, applicant_count):
    """
    send the new excel file to backoffice's email
    """
    sent = smtplib.SMTP_SSL(mail_host,mail_port)
    sent.login(from_add, _pwd)
    try:
        msg = MIMEMultipart()
        #mail subject
        msg['From'] = from_add
        msg['To'] = ','.join(to_add)
        msg['Subject'] = 'The List Of Applicants'
        #mail content
        if applicant_count == 0:
            content = MIMEText('hello, there is NO new applicant today.')
            msg.attach(content)
        else:
            content = MIMEText('hello, this is the list of applicants')
            msg.attach(content)
        
        #mail attachment
            if os.path.isfile(file_path):
                type, coding = mimetypes.guess_type(file_path)
                if type == None:
                    type = 'application/octet-stream'
            
                major_type, minor_type = type.split('/',1)
                with open(file_path, 'rb') as file:
                    if major_type == 'text':
                        attachment = MIMEText(file.read(), _subtype=minor_type)
                    if major_type == 'image':
                        attachment = MIMEImage(file.read(), _subtype=minor_type)
                    if major_type == 'application':
                        attachment = MIMEApplication(file.read(), _subtype=minor_type)
                    if major_type == 'MIMEAudio':
                        attachment = MIMEAudio(file.read(), _subtype=minor_type)
            
                attachment.add_header('Content-Disposition', 'attachment',filename = (file_name))
                msg.attach(attachment)

        full_text = msg.as_string()

        sent.sendmail(from_add,to_add,full_text)
        logging.info("email sent successfully to: " + msg['To'])
        sent.close()
    except smtplib.SMTPException as e:
        print "Falied: " + str(e)
        logging.warning(str(e))

    return

def update_status(applicant_count):
    if applicant_count > 0:
        count,res = queryAllwithDict('select * from customers where email_sent_indicator = "0"')
        for dict in res:
            insert_sql = "INSERT INTO customers_sent (customer_id, first_name, last_name, phone_number, apply_date, apply_status, apply_decline_info) VALUES (\
            '" + str(dict['id']) + "',\
            '" + str(dict['first_name']) + "',\
            '" + str(dict['last_name']) + "',\
            '" + str(dict['phone_number']) + "',\
            '" + str(dict['apply_date']) + "',\
            '" + str(dict['apply_status']) + "',\
            '" + str(dict['apply_decline_info']) + "')"
            logging.info(insert_sql)
            update_sql = "UPDATE customers SET email_sent_indicator = 1 WHERE id = " + str(dict['id'])
            logging.info(update_sql)
            sql = insert_sql + ";" + update_sql + ";"
            execute(sql)
        logging.info("update email sent status to be yes(1).")
    return

def job():
    file_path, file_name, count = write_into_excel()
    send_email(file_path,file_name,count)
    update_status(count)



if __name__ == "__main__":

    #job()

    schedule.every().day.at("06:00").do(job)

    while True:
        schedule.run_pending()
        logging.info(time.strftime('%Y.%m.%d %H:%M:%S',time.localtime(time.time())) + " Time has not come")
        #print time.strftime('%Y.%m.%d %H:%M:%S',time.localtime(time.time())) + " Time has not come"
        time.sleep(30)